package jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Date;

public class JdbcTest implements Runnable {

	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf-8","root","123456");
		String sql = "select * from user_info where user_id = 1";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(sql);
		User user = null;
		while(rs.next()) {
			user = new User();
			user.setId(rs.getInt("user_id"));
			user.setName(rs.getString("username"));
			user.setAge(rs.getInt("age"));
			user.setBirth(rs.getTimestamp("birth_date"));
			user.setPassword(rs.getString("password"));
			user.setSex(rs.getInt("sex"));
			user.setVersion(rs.getInt("version"));
		}
		conn.setAutoCommit(false);
		String updateSql = "update user_info t set t.username = ?,t.age = ?" +
				" , t.birth_date = ? , t.password = ? , t.sex = ? , t.version = t.version + 1 where t.user_id = 1 and t.version = ?";
		PreparedStatement ps = conn.prepareStatement(updateSql);
//		ps.setInt(1, 1);
		ps.setString(1, "主线程");
		ps.setInt(2, 3);
		ps.setTimestamp(3, new Timestamp(new Date().getTime()));
		ps.setString(4, "111111");
		ps.setInt(5, 2);
		ps.setInt(6, user.getVersion());
		new Thread(new JdbcTest()).start();
		Thread.sleep(5000);
		int i = ps.executeUpdate();
		System.out.println("主线程-----" + i + "-------------");
		conn.commit();
		conn.setAutoCommit(true);
		
		rs.close();
		st.close();
		ps.close();
		conn.close();
	}
	
	public void run() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1/test?useUnicode=true&characterEncoding=utf-8","root","123456");
			
			String sql = "select * from user_info where user_id = 1";
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(sql);
			User user = null;
			while(rs.next()) {
				user = new User();
				user.setId(rs.getInt("user_id"));
				user.setName(rs.getString("username"));
				user.setAge(rs.getInt("age"));
				user.setBirth(rs.getTimestamp("birth_date"));
				user.setPassword(rs.getString("password"));
				user.setSex(rs.getInt("sex"));
				user.setVersion(rs.getInt("version"));
			}
			
			conn.setAutoCommit(false);
			String updateSql = "update user_info t set t.username = ?,t.age = ?" +
			" , t.birth_date = ? , t.password = ? , t.sex = ? ,t.version = t.version + 1 where t.user_id = 1 and t.version = ?";
			PreparedStatement ps = conn.prepareStatement(updateSql);
			ps.setString(1, "xin线程");
			ps.setInt(2, 3);
			ps.setTimestamp(3, new Timestamp(new Date().getTime()));
			ps.setString(4, "111111");
			ps.setInt(5, 2);
			ps.setInt(6, user.getVersion());
			int i = ps.executeUpdate();
			System.out.println("xin线程-----" + i + "-------------");
			conn.commit();
			conn.setAutoCommit(true);
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public String toString() {
		// TODO Auto-generated method stub
		return super.toString();
	}

}
